﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class DbHelper
    {
    /// <summary>
    /// 连接数据库的方法
    /// </summary>
    private static SqlConnection sqlCon;
    public static SqlConnection sqlConnect
    {
        get 
        {
            string strSQL = @"Server=192.168.1.188;database=ktsoft;uid=sa;pwd=fdsazxcv";
            sqlCon = new SqlConnection(strSQL);
            if (sqlCon == null)
            {
                sqlCon.Open();
            }
            else if (sqlCon.State == System.Data.ConnectionState.Closed)
            {
                sqlCon.Open();
            }
            else if (sqlCon.State == System.Data.ConnectionState.Broken)
            {
                sqlCon.Close();
                sqlCon.Open();
            }
            
            return sqlCon;

        }
    }

    /// <summary>
    /// 存储过程；返回sqlreader，无参数；
    /// </summary>
    /// <param name="strSQL"></param>
    /// <returns></returns>
    public static SqlDataReader getReader(string strSQL)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConnect);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader sqlRd = sqlCmd.ExecuteReader();
            return sqlRd;
            sqlRd.Close();
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            sqlConnect.Close();
        }
    }

    /// <summary>
    /// 存储过程；返回sqlreader，有参数；
    /// </summary>
    /// <param name="strSQL"></param>
    /// <returns></returns>
    public static SqlDataReader getReader(string strSQL,params SqlParameter[] sqlParam)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConnect);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddRange(sqlParam);
            SqlDataReader sqlRd = sqlCmd.ExecuteReader();
            return sqlRd;
            sqlRd.Close();
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            sqlConnect.Close();
        }
    }

    public static SqlDataAdapter getAdapter(string strSQL, params SqlParameter[] sqlParam)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConnect);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddRange(sqlParam);
            SqlDataAdapter sqlAdp = new SqlDataAdapter(sqlCmd);
            return sqlAdp;
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            sqlConnect.Close();
        }
    }

    /// <summary>
    /// 存储过程；返回table，无参数
    /// </summary>
    /// <param name="strSQL"></param>
    /// <returns></returns>
    public static DataTable getDateSet(string strSQL)
    {
        try
        {
            DataSet ds = new DataSet();
            SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConnect);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);
            sqlAdapter.Fill(ds);

            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            sqlConnect.Close();
        }
      }

    /// <summary>
    /// 存储过程；返回table，有参数
    /// </summary>
    /// <param name="strSQL"></param>
    /// <param name="sqlParam"></param>
    /// <returns></returns>
    public static DataTable getDateSet(string strSQL, params SqlParameter[] sqlParam)
    {
        try
        {
            DataSet ds = new DataSet();
            SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConnect);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddRange(sqlParam);
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);
            sqlAdapter.Fill(ds);

            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            sqlConnect.Close();
        }
    }

    /// <summary>
    /// 返回第一列（整型），无参数
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int GetScalar(string sql)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand(sql, sqlConnect);
            sqlCmd.CommandType = CommandType.StoredProcedure;

            return Convert.ToInt32(sqlCmd.ExecuteScalar());
        }
        catch (Exception ex)
        {
            return 0;
        }
        finally
        {
            sqlConnect.Close();
        }
    }

    /// <summary>
    /// 返回第一列（整型），有参数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public static int GetIntScalar(string sql, params SqlParameter[] values)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand(sql,sqlConnect);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddRange(values);
            return Convert.ToInt32(sqlCmd.ExecuteScalar());
        }
        catch (Exception ex)
        {
            return 0;
        }
        finally
        {
            sqlConnect.Close();
        }
    }

    /// <summary>
    ///  返回第一列（字符型），有参数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public static string GetStrScalar(string sql, params SqlParameter[] values)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand(sql, sqlConnect);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddRange(values);
            return sqlCmd.ExecuteScalar().ToString();
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            sqlConnect.Close();
        }
    }

    /// <summary>
    /// 返回受影响行数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            try
            {
                SqlCommand sqlCmd = new SqlCommand(sql,sqlConnect);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddRange(values);

                return sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            { 
                return 0;
            }
            finally
            {
                sqlConnect.Close();
            }
        }
}
}
